<?php
/**
 * Created by PhpStorm.
 * User: Administrator
 * Date: 2020/4/22 0022
 * Time: 14:58
 */

namespace app\finance\controller;


use think\Db;

class IndexController extends BaseController
{
    public function index()
    {
        return $this->fetch();
    }

    public function top()
    {
        return $this->fetch();
    }

    public function left_menu()
    {
        $count = Db::table('expense')->where('status', '=', 1)->count();
        $this->assign('count', $count);
        return $this->fetch();
    }

    /**
     * @return mixed
     * 工资列表
     */
    public function salary()
    {
        return $this->fetch();
    }



    /**
     * @return array
     * 工资数据接口
     */
    public function GongZhiChaXun()
    {

        $map = array();
        if(session('user_name') != 'admin') $map[] = array('real_name', 'eq', session('user_name'));
        if(!empty($_GET) and (isset($_GET['date_time']) or isset($_GET['name']) or isset($_GET['card_num']))){
            if(!empty($_GET['date_time'])){
                $date_range = explode(' - ',$_GET['date_time']);
                $start_time = $date_range[0];
                $end_time = $date_range[1];
                $map[] = array("date_year_month",'between time',[$start_time,$end_time]);
            }

            if(session('user_name') == 'admin') if(!empty($_GET['name'])) $map[] = ['real_name','=',$_GET['name']];
            if(!empty($_GET['card_num'])) $map[] = array('card_num', '=',$_GET['card_num']);

            $array = Db::table('user_data')->where($map)->order(array('date_year' => 'desc','date_month'=>'desc'))->select();
            $count = Db::table('user_data')->where($map)->count();
            $data = array('code' => 0, 'msg' => '', 'count' => $count, 'data' => $array);
            return $data;
        }
        $page = empty($_GET['page']) ? 0 :$_GET['page'] - 1;
        $limit = empty($_GET['limit']) ? 15 :$_GET['limit'];
        $array = Db::table('user_data')->where($map)->limit($page, $limit)->order(array('date_year' => 'desc'))->select();
        $count = Db::table('user_data')->count();
        $data = array('code' => 0, 'msg' => '', 'count' => $count, 'data' => $array);
        return $data;
    }
    
    /**
     * 上传excel文件
     */
    public function uploadExcel()
    {
        $file = $this->request->file('file');
        $isAlreadyUpload = Db::table('file_path')
            ->where(array('file_name' => array('eq', $file->getInfo('name'))))
            ->order('id', 'desc')
            ->find();
        //检查一下文件是否已经导入过？
        if($isAlreadyUpload){
            $fileInfo = $file->validate(['ext'=>'xlsx,xls'])->move('./excel/',true,false);
            if($fileInfo){
                $data = array();
                $data['file_name'] = $_FILES['file']['name'];
                $data['file_path'] = $fileInfo->getSaveName();
                $data['create_time'] = date('Y-m-d H:i:s');
                $insertRes = Db::table('file_path')->data($data)->insert();
                if(!$insertRes)  return $this->ajaxFail();
//                return $this->ajaxSuccess(1,'',array('file_id'=> Db::getLastInsID()));
                return $this->ajaxSuccess(2, '该文件已经导入，是否重新导入',array('old_file_id' => $isAlreadyUpload['id'],'file_id'=> Db::getLastInsID()));
            }else{
                return $this->ajaxFail(0, $file->getError());
            }
        }

        $fileInfo = $file->validate(['ext'=>'xlsx,xls'])->move('./excel/',true,false);
        if($fileInfo){
            $data = array();
            $data['file_name'] = $_FILES['file']['name'];
            $data['file_path'] = $fileInfo->getSaveName();
            $data['create_time'] = date('Y-m-d H:i:s');
            $insertRes = Db::table('file_path')->data($data)->insert();
            if(!$insertRes)  return $this->ajaxFail();
            return $this->ajaxSuccess(1,'',array('file_id'=> Db::getLastInsID()));
        }else{
            return $this->ajaxFail(0, $file->getError());
        }
    }

    /**
     * 获取上传文件的数据
     */
    public function getUploadFileData()
    {
        $file_id = $this->request->post('file_id');
        $type = $this->request->post('type');
        if($type == 0){
            $old_file_id = $this->request->post('old_file_id');
            Db::table('user_data')->where(array('from_excel_id' => array('eq', $old_file_id)))->delete();
        }
        $fileInfo = Db::table('file_path')->where(array('id' => array('eq', $file_id)))->find();
        if(empty($fileInfo)) $this->ajaxFail(0, '文件不有问题,请联系管理员');
        $filePath = getRealDir() .'/public/excel/' . $fileInfo['file_path'];
        if(!file_exists($filePath)) $this->ajaxFail(0, '文件不存在,请联系管理员');
        $data = import_excel($filePath);
//        dump($data);die;
        $insertData = array();
        $fieldArray = array(
             'department',
             'real_name',
//             'card_num',
             'date_year',
             'date_month',
             'technical_grade',
             'level_of_wages',
             'probation_pay',
             'allowance',
             'subsidy',
             'price_of_commodities',
             'special_post_allowance',
             'other_subsidy',
             'police_subsidy',
             'civilization_award',
             'housing_add',
             'back_pay',
             'deduction_of_wages',
             'other',
             'above_civilization_award',
             'transport_subsidy',
             'year_bonus',
             'civilized_city_award',
             'assessment_award',
             'peacetime_award',
             'heating_fee',
             'service_subsidy',
             'official_call_subsidy',
             'should_send',
             'accumulation_fund',
             'basic_insurance',
             'medical_premiums',
             'unemployment_insurance',
             'occupational_annuity',
             'income_tax',
             'garnishment',
             'net_payroll',
        );

        foreach ($data  as $key => $datum) {
//            //导入数据同时是否自动创建用户
//            $isExist = Db::table('user_info')
//                ->where('user_name', '=', $datum[0])
//                ->where('card_num', '=', $datum[1])
//                ->find();
//            if(empty($isExist)){
//                $addUserData = array();
//                $addUserData['user_name'] = $datum[1];
//                $addUserData['pass_word'] = '123456';
//                Db::table('user_info')->data($addUserData)->insert();
//            }
            foreach ($datum as $index => $item) {
                $insertData[$key][$fieldArray[$index]] = $item;
                $insertData[$key]['date_year_month'] = $datum[2] .'-' . $datum['3'] . '-01';
                $insertData[$key]['from_excel_id'] = $file_id;
            }
        }
//        dump($insertData);die;
        $result = Db::table('user_data')->data($insertData)->limit(100)->insertAll();
        if(!$result)  return $this->ajaxFail();
        return $this->ajaxSuccess();
    }

    /**
     * 导出文件
     */
    public function exportFile()
    {
        //是否根据条件导出
        $map = array();
        if(session('user_name') != 'admin') $map['real_name'] = array('eq', session('user_name'));
        if(!empty($_POST) and (!empty($_POST['date_time']) or !empty($_POST['name']) or !empty($_POST['card_num']) or !empty($_POST['checkIdS']))){
            $date_time = explode('-',$_POST['date_time']);
//            if(!empty($_POST['date_time'])) $map[] = array('date_year','eq',$date_time[0]);
//            if(!empty($_POST['date_time'])) $map[] = array('date_month','eq', (int)$date_time[1]);
            if(!empty($_POST['name'])) $map[] = ['real_name','=',$_POST['name']];
            if(!empty($_POST['card_num'])) $map[] = array('card_num', '=',$_POST['card_num']);
            if(!empty($_POST['checkIdS'])) $map[] = array('id','in', trim($_POST['checkIdS'],','));
        }
//        dump($map);
        $xlsData = Db::table('user_data')->where($map)->order('id','desc')->select();
//        dump($xlsData);
//        die;
        $objExcel = new \PHPExcel();
        $objWriter = \PHPExcel_IOFactory::createWriter($objExcel, 'Excel2007');
        $objActSheet = $objExcel->getActiveSheet();
        $letter =explode(',',"A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z");
        $arrHeader = array(
        '人员名称'
        ,'身份证号'
        ,'业务年度'
        ,'月份'
        ,'职务（技术等级）工资'
        ,'级别（岗位）工资'
        ,'见习期工资'
        ,'工作性津贴'
        ,'生活性补贴'
        ,'保留物价福利补贴'
        ,'特殊岗位津贴'
        ,'转隶人员绝对值'
        ,'文明奖'
        ,'住房补贴'
        ,'补发工资'
        ,'公务交通补贴'
        ,'物业服务补贴'
        ,'公务通讯补贴'
        ,'应发项'
        ,'住房公积金'
        ,'基本养老保险'
        ,'医疗保险费'
        ,'职业年金'
        ,'所得税'
        ,'扣发项'
        ,'实发工资'
        );
        $lenth =  count($arrHeader);
        //设置第一行标题  XXXX年XX月工资发放表
        $objExcel->getActiveSheet()->mergeCells('A1:Z1');//合并单元格
        $objActSheet->setCellValue('A1', 'XXXX年XX月工资发放表');
        //垂直居中显示
        $objExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        //设置字体大小
        $objExcel->getActiveSheet()->getStyle("A1")->getFont()->setBold(true)
            ->setSize(18);
        for($i = 0;$i < $lenth;$i++) {
            $objActSheet->setCellValue("$letter[$i]2","$arrHeader[$i]");
        };
        foreach($xlsData as $k=>$v){
            $k +=3;
            $objActSheet->setCellValue('A'.$k, $v['real_name']);
            $objActSheet->setCellValue('B'.$k, $v['card_num']);
            $objActSheet->setCellValue('C'.$k, $v['date_year']);
            $objActSheet->setCellValue('D'.$k, $v['date_month']);
            $objActSheet->setCellValue('E'.$k, $v['technical_grade']);
            $objActSheet->setCellValue('F'.$k, $v['level_of_wages']);
            $objActSheet->setCellValue('G'.$k, $v['probation_pay']);
            $objActSheet->setCellValue('H'.$k, $v['allowance']);
            $objActSheet->setCellValue('I'.$k, $v['subsidy']);
            $objActSheet->setCellValue('J'.$k, $v['price_of_commodities']);
            $objActSheet->setCellValue('K'.$k, $v['special_post_allowance']);
            $objActSheet->setCellValue('L'.$k, '转隶人员绝对值');
            $objActSheet->setCellValue('M'.$k, $v['above_civilization_award']);
            $objActSheet->setCellValue('N'.$k, $v['housing_add']);
            $objActSheet->setCellValue('O'.$k, $v['back_pay']);
            $objActSheet->setCellValue('P'.$k, $v['transport_subsidy']);
            $objActSheet->setCellValue('Q'.$k, $v['service_subsidy']);
            $objActSheet->setCellValue('R'.$k, $v['official_call_subsidy']);
            $objActSheet->setCellValue('S'.$k, $v['should_send']);
            $objActSheet->setCellValue('T'.$k, $v['accumulation_fund']);
            $objActSheet->setCellValue('U'.$k, $v['basic_insurance']);
            $objActSheet->setCellValue('V'.$k, $v['medical_premiums']);
            $objActSheet->setCellValue('W'.$k, $v['occupational_annuity']);
            $objActSheet->setCellValue('X'.$k, $v['income_tax']);
            $objActSheet->setCellValue('Y'.$k, $v['garnishment']);
            $objActSheet->setCellValue('Z'.$k, $v['net_payroll']);

            $objActSheet->getRowDimension($k)->setRowHeight(20);
        }
        $width = array(10,20,10,10,25,20,20,20);
        //设置表格的宽度
        $objActSheet->getColumnDimension('A')->setWidth($width[0]);
        $objActSheet->getColumnDimension('B')->setWidth($width[1]);
        $objActSheet->getColumnDimension('C')->setWidth($width[2]);
        $objActSheet->getColumnDimension('D')->setWidth($width[3]);
        $objActSheet->getColumnDimension('E')->setWidth($width[4]);
        $objActSheet->getColumnDimension('F')->setWidth($width[5]);
        $objActSheet->getColumnDimension('G')->setWidth($width[6]);
        $objActSheet->getColumnDimension('H')->setWidth(20);
        $objActSheet->getColumnDimension('I')->setWidth(20);
        $objActSheet->getColumnDimension('J')->setWidth(20);
        $objActSheet->getColumnDimension('L')->setWidth(20);
        $objActSheet->getColumnDimension('M')->setWidth(10);
        $objActSheet->getColumnDimension('N')->setWidth(10);
        $objActSheet->getColumnDimension('O')->setWidth(10);
        $objActSheet->getColumnDimension('P')->setWidth(20);
        $objActSheet->getColumnDimension('Q')->setWidth(20);
        $objActSheet->getColumnDimension('R')->setWidth(20);
        $objActSheet->getColumnDimension('S')->setWidth(10);
        $objActSheet->getColumnDimension('T')->setWidth(20);
        $objActSheet->getColumnDimension('U')->setWidth(20);
        $objActSheet->getColumnDimension('V')->setWidth(20);
        $objActSheet->getColumnDimension('W')->setWidth(15);
        $objActSheet->getColumnDimension('X')->setWidth(10);
        $objActSheet->getColumnDimension('Y')->setWidth(10);
        $objActSheet->getColumnDimension('Z')->setWidth(15);

        $outfile = "工资发放表.xlsx";
        ob_end_clean();
        header("Content-Type: application/force-download");
        header("Content-Type: application/octet-stream");
        header("Content-Type: application/download");
        header('Content-Disposition:inline;filename="'.$outfile.'"');
        header("Content-Transfer-Encoding: binary");
        header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
        header("Pragma: no-cache");
        $objWriter->save('php://output');    //这里直接导出文件
    }

    /**
     *删除工资发发放表
     */
    public function deleteSalary()
    {
        if(!empty($_POST) and isset($_POST['checkIdS'])){
            $checkIdArray = explode(',', trim($_POST['checkIdS'], ','));
            $delResult = Db::table('user_data')->delete($checkIdArray);
            if($delResult) return $this->ajaxSuccess();
        }
        return $this->ajaxFail();
    }
}